package ktwl.code.utils;

import com.alibaba.fastjson.JSONObject;
import ktwl.code.connection.CustomDataSource;
import ktwl.code.connection.JdbcConnection;

import java.sql.*;
import java.util.List;

/**
 * 数据库连接工具
 *
 * @author zhuhe on 2019/8/21 14:40
 * @version 1.0
 */
public class DbUtil {

    private static final CustomDataSource CUSTOMDATASOURCE = new CustomDataSource();

    /**
      * 关闭连接池 on 2019/8/23 11:32
      */
    public static void connectionPoolClose(){
        CUSTOMDATASOURCE.close();
    }

    /**
      * 操作数据库支持建表,新增,修改,删除 on 2019/8/26 10:00
      *
      * @param sql sql
      * @return Integer
      * @throws Exception Exception
      */
    public static Integer operationData(String sql) throws Exception{
        if(sql == null || "".equals(sql)){
            throw new Exception("sql为空");
        }
        if(sql.contains("insert") || sql.contains("INSERT")){
            return insert(sql);
        }
        if(sql.contains("update") || sql.contains("UPDATE")){
            return update(sql);
        }
        if(sql.contains("delete") || sql.contains("DELETE")){
            return delete(sql);
        }
        if(sql.contains("create") || sql.contains("CREATE")){
            return create(sql);
        }
        throw new Exception("不是create,insert,update,delete中的任意一种");
    }

    /**
     * 不处理参数执行 on 2019/8/26 12:27
     *
     * @param sql sql
     * @return Integer
     * @throws Exception Exception
     */
    public static Integer directExecutionSql(String sql)throws Exception{
        //获取连接
        Connection conn = CUSTOMDATASOURCE.getConnection();
        //创建执行SQL的prepareStatement对象
        PreparedStatement ps = conn.prepareStatement(sql);
        //用于增删改操作
        int result = ps.executeUpdate();
        //释放资源
        JdbcConnection.release(null,ps,null,null);
        //归还连接池资源
        CUSTOMDATASOURCE.addBack(conn);
        return result;
    }

    /**
      * 无参数查询 on 2019/8/21 17:48
      *
      * @param sql sql
      * @return List<JSONObject>
      * @throws SQLException SQLException
      */
    public static List<JSONObject> noGinsengQuery(String sql) throws SQLException {
        Connection conn = CUSTOMDATASOURCE.getConnection();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        ResultSetMetaData metaData = rs.getMetaData();
        List<JSONObject> objects = DbHandle.handleNoGinsengQuery(rs,metaData);
        //释放资源
        JdbcConnection.release(null,null,stmt,rs);
        //归还连接池资源
        CUSTOMDATASOURCE.addBack(conn);
        return objects;
    }

    /**
      * 带参数查询 on 2019/8/22 10:08
      *
      * @param sql sql
      * @param strings strings
      * @return List<JSONObject>
      * @throws SQLException SQLException
      */
    public static List<JSONObject> parameterQuery(String sql,List<String> strings) throws SQLException{
        Connection conn = CUSTOMDATASOURCE.getConnection();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        ResultSetMetaData metaData = rs.getMetaData();
        List<JSONObject> objects = null;
        if(strings != null && strings.size() > 0){
            objects = DbHandle.handleParameterQuery(rs,metaData,strings);
        }else{
            objects = DbHandle.handleNoGinsengQuery(rs,metaData);
        }
        //释放资源
        JdbcConnection.release(null,null,stmt,rs);
        //归还连接池资源
        CUSTOMDATASOURCE.addBack(conn);
        return objects;
    }

    /**
     * 新建 on 2019/8/26 17:19
     *
     * @param sql sql
     * @return Integer
     * @throws Exception Exception
     */
    private static Integer create(String sql) throws Exception{
        return directExecutionSql(sql);
    }

    /**
     * 删除 on 2019/8/26 17:04
     *
     * @param sql sql
     * @return Integer
     * @throws Exception Exception
     */
    private static Integer delete(String sql) throws Exception{
        return directExecutionSql(sql);
    }

    /**
     * 修改 on 2019/8/26 17:01
     *
     * @param sql sql
     * @return Integer
     * @throws Exception Exception
     */
    private static Integer update(String sql) throws Exception{
        JSONObject object = DbHandle.handleUpdate(sql);
        Integer b = 0;
        if(object.get("value") == null){
            b = directExecutionSql(sql);
        }else{
            b = handleExecutionSql(object);
        }
        return b;
    }

    /**
     * 新增 on 2019/8/21 17:32
     *
     * @param sql sql
     * @return boolean boolean
     * @throws SQLException SQLException
     */
    private static Integer insert(String sql) throws Exception{
        JSONObject object = DbHandle.handleInsert(sql);
        Integer b = 0;
        if(object.get("value") == null){
            b = directExecutionSql(sql);
        }else {
            b = handleExecutionSql(object);
        }
        return b;
    }

    /**
     * 预编译处理 on 2019/8/26 17:06
     *
     * @param jsonObject jsonObject
     * @return Integer
     * @throws Exception Exception
     */
    private static Integer handleExecutionSql(JSONObject jsonObject) throws Exception{
        Integer b = 0;
        //获取连接
        Connection conn = CUSTOMDATASOURCE.getConnection();
        b = handleExecutionSql(jsonObject,conn);
        //归还连接池资源
        CUSTOMDATASOURCE.addBack(conn);
        return b;
    }

    /**
     * 预编译sql执行 on 2019/8/26 15:19
     *
     * @param object object
     * @param conn conn
     * @return Integer
     * @throws SQLException SQLException
     */
    private static Integer handleExecutionSql(JSONObject object,Connection conn) throws SQLException{
        //预编译SQL，减少sql执行
        PreparedStatement ptmt = conn.prepareStatement(object.getString("sql"));
        //传参
        JSONObject b = object.getJSONObject("value");
        for(int i = 0;i < b.size();i++){
            ptmt.setString(i + 1,b.getString(String.valueOf(i)));
        }
        //执行
        Integer b1 = ptmt.executeUpdate();
        //释放资源
        JdbcConnection.release(null,ptmt,null,null);
        return b1;
    }
}